<?php
namespace app\services;

use app\utils\Functions as F;
use app\utils\Upload as Up;
use app\services\UsersService as US;

class ReportService extends BaseService
{
    public $matrix = [
        'id' => 'campaign_name',
        'campaign_name' => 'campaign_name',
        'launchId' => 'campaign_id',
        'solutionId' => 'solutionid',
        'creativeId' => 'creativeuuid',
        'solutionName' => 'solution_name',
        'creativeName' => 'creative_name',
        //'channel' => ['seller_name', '$v=="vamaker" && $v=\Yii::t("common", "ValueMaker");
                                     //$v=="valuemaker"&&$v=\Yii::t("common", "ValueMaker");
                                     //return $v;'],
        //'market' => ['seller_name', '$v=="vamaker" && $v=\Yii::t("common", "ValueMaker");
                                     //$v=="valuemaker"&&$v=\Yii::t("common", "ValueMaker");
                                     //return $v;'],
        'adspaceid' => 'adspaceid',
        'adspace'   => 'adspace_name',
        //'advertiser' => 'advertiser_name',
        //'originCost' => 'origin_cost',
        'cost' => 'cost',
        //'reqCount' => 'reqs',
        //'bidCount' => 'bids',
        'showCount' => 'shows',
        'clickCount' => 'clicks',
        'clickRate' => 'clickRate',
        'cpm' => 'cpm',
        'cpc' => 'cpc',
        'date' => 'date',
    ];

    public $exportMatrix = [
        '投放名称' => 'campaign_name',
        '日期' => 'date',
        '策略名称' => 'solution_name',
        '创意名称' => 'creative_name',
        '所属渠道' => ['seller_name', '$v=="vamaker" && $v=\Yii::t("common", "ValueMaker");
                                     $v=="valuemaker" && $v=\Yii::t("common", "ValueMaker");
                                     return $v;'],
        '广告位ID' => 'adspaceid',
        '广告位名称'   => 'adspace_name',
        '品牌' => 'advertiser_name',
        '原始花费' => 'origin_cost',
        '花费' => 'cost',
        '请求数' => 'reqs',
        '竞价数' => 'bids',
        '展示数' => 'shows',
        '点击数' => 'clicks',
        '点击率(%)' => 'clickRate',
        'cpm' => 'cpm',
        'cpc' => 'cpc',
    ];

    public $enExportMatrix = [
        'Campaign' => 'campaign_name',
        'Date' => 'date',
        'Solution' => 'solution_name',
        'Belong Market' => ['seller_name', '$v=="vamaker" && $v=\Yii::t("common", "ValueMaker");
                                     $v=="valuemaker"&&$v=\Yii::t("common", "ValueMaker");
                                     return $v;'],
        'Adspace ID' => 'adspaceid',
        'Adspace'   => 'adspace_name',
        'Brand' => 'advertiser_name',
        'Origin Cost' => 'origin_cost',
        'Cost' => 'cost',
        'Requests' => 'reqs',
        'Bids' => 'bids',
        'Shows' => 'shows',
        'Clicks' => 'clicks',
        'Click Rate(%)' => 'clickRate',
        'cpm' => 'cpm',
        'cpc' => 'cpc',
    ];

    public $exportFilterMatrix = [
        '投放名称' => 'campaign_name',
        '日期' => 'date',
        // '所属渠道' => ['seller_name', '$v=="vamaker" && $v=\Yii::t("common", "ValueMaker");
        //                              $v=="valuemaker"&&$v=\Yii::t("common", "ValueMaker");
        //                              return $v;'],
        // '广告位ID' => 'adspaceid',
        // '广告位名称'   => 'adspace_name',
        // '品牌' => 'advertiser_name',
        '花费' => 'cost',
        '请求数' => 'reqs',
        '竞价数' => 'bids',
        '展示数' => 'shows',
        '点击数' => 'clicks',
        '点击率(%)' => 'clickRate',
        'cpm' => 'cpm',
        'cpc' => 'cpc',
    ];

    public $enExportFilterMatrix = [
        'Campaign' => 'campaign_name',
        // 'Belong Market' => ['seller_name', '$v=="vamaker" && $v=\Yii::t("common", "ValueMaker");
        //                              $v=="valuemaker"&&$v=\Yii::t("common", "ValueMaker");
        //                              return $v;'],
        // 'Adspace ID' => 'adspaceid',
        // 'Adspace'   => 'adspace_name',
        // 'Brand' => 'advertiser_name',
        'Date' => 'date',
        'Cost' => 'cost',
        'Requests' => 'reqs',
        'Bids' => 'bids',
        'Shows' => 'shows',
        'Clicks' => 'clicks',
        'Click Rate(%)' => 'clickRate',
        'cpm' => 'cpm',
        'cpc' => 'cpc',
    ];

    public function checkSolutions() {
        $ads = $this->S('domainAdvertisers');
        $ids = [];
        if (!$ads) return false;
        foreach($ads as $ad) {
            $ids[] = $ad['id'];
        }   
        $userId = implode(' , ', $ids);
        $where = " advertiser_id in ( $userId ) ";
        $solutions = \app\models\Solution::find()->where($where)->indexBy('id')->asArray()->all();
        $labelSolutions = array_keys($solutions);
        if(!$labelSolutions) return false; 
        return true;
    }

    public function getReportData() {
        if(!$this->checkSolutions()) {
            $list = [];
            $total = [[0,0], 0,0,0,[0,0],[0,0]];
            if(in_array($this->S('user_rank'), ['agent', 'advertiser'])) {
                $total = [0,0,0,0,0,0,0,0];
            }
            $date = date("Y-m-d");
            $graph = ['x'=>[$date], 'y0' =>[0], 'y1' =>[0], 'y2'=>[0], 'y3'=>[0], 'y4'=>[0], 'y5'=>[0]];
            $graph = $this->formData($graph);
            return ['data' => $list,
                    'total' => $total,
                    'graph' => $graph];
        }
        
        $list = $this->getReportDataList($this->matrix);
        $list = $this->groupAdspaceData($list);
        $total = $this->getReportTotal();
        $graph = $this->getReportGraph('rd.logdate', $this->_searchWhere());
        $graph = $this->formData($graph);
        return ['data' => $list,
                'total' => $total,
                'graph' => $graph];
    }

    public function getAppDayReport()
    {
        $list = $this->getReportDataList($this->matrix);
        $list = $this->groupAdspaceData($list, 'app');
        return $list;
    }

    public function getAdspaceMatrix($adids)
    {
        $appAdMaps = $this->getAll('ResourceAppAdspaceMap', ['status' => 1, 'adspace_id' => $adids], 'adspace_id');
        $adId2appId = array_column($appAdMaps, 'app_id', 'adspace_id');
        $appIds = array_values($adId2appId);
        $apps = $this->getAll('ResourceApp', ['status' => 1, 'id' => $appIds]);
        $appId2appName = array_column($apps, 'name', 'id');
        $appId2packId = array_column($apps, 'resource_pack_id', 'id');
        $packIds = array_column($apps, 'resource_pack_id');
        $packs = $this->getAll('ResourcePack', ['status' => 1, 'id' => $packIds]);
        $packId2packName = array_column($packs, 'name', 'id');
        return [
            'adId2appId'      => $adId2appId,
            'appId2packId'    => $appId2packId,
            'appId2appName'   => $appId2appName,
            'packId2packName' => $packId2packName,
        ];
    }

    public function groupAdspaceData($datas, $to='pack')
    {
        $adids = array_column($datas, 'adspaceid');
        $extra = $this->getAdspaceMatrix($adids);
        $groupData = [];
        $key = 'pack';
        $keyId = 'packId';
        if ($to == 'app') {
            $key = 'name';
            $keyId = 'id';
        }

        $groupData = [];
        foreach ($datas as $data) {
            $adid = $data['adspaceid'];
            $keyIdValue = $extra['adId2appId'][$adid];
            $keyValue = $extra['appId2appName'][$keyIdValue];
            if ($to == 'pack') {
                $keyIdValue = $extra['appId2packId'][$keyIdValue];
                $keyValue = $extra['packId2packName'][$keyIdValue];
            }
            $data[$keyId] = $keyIdValue;
            $data[$key] = $keyValue;
            $dim = ['date', 'campaignId', 'solutionId', 'creativeId', $key, $keyId];
            $dim = array_intersect_key($data, array_flip($dim));
            $dim = implode('$$', array_values($dim));
            unset($data['adspaceid']);
            unset($data['adspace']);
            if (isset($groupData[$dim])) {
                $groupData[$dim]['cost'] += $data['cost'];
                $groupData[$dim]['showCount'] += $data['showCount'];
                $groupData[$dim]['clickCount'] += $data['clickCount'];
                if ($groupData[$dim]['showCount'] != 0) {
                    $groupData[$dim]['clickRate'] = round($groupData[$dim]['clickCount']/$groupData[$dim]['showCount'], 2);
                } else {
                    $groupData[$dim]['clickRate'] = '0.00';
                }
                if ($groupData[$dim]['showCount'] != 0) {
                    $groupData[$dim]['cpm'] = round($groupData[$dim]['cost']*1000/$groupData[$dim]['showCount'], 2);
                } else {
                    $groupData[$dim]['cpm'] = '0.00';
                }
                if ($groupData[$dim]['clickCount'] != 0) {
                    $groupData[$dim]['cpc'] = round($groupData[$dim]['cost']/$groupData[$dim]['clickCount'], 2);
                } else {
                    $groupData[$dim]['cpc'] = '0.00';
                }
            } else {
                $groupData[$dim] = $data;
            }
        }
        return array_values($groupData);
    }

    public function _searchWhere($isTotal=false) {
        $where = [];
        $labelId = $this->I('campaign');
        $campaignId = $this->I('launch');
        $solutionId = $this->I('strategy');
        $creativeId = $this->I('creative');
        $pack = $this->I('pack');

        if ($labelId && $labelId != 'all') {
            $where[] = "label_id = $labelId";
        }
        if ($campaignId && $campaignId != 'all') {
            $where[] = "campaign_id = $campaignId";
        }
        if ($solutionId && $solutionId != 'all') {
            $where[] = "solutionid = $solutionId";
        }
        if ($creativeId && $creativeId != 'all') {
            $where[] = "creativeuuid = $creativeId";
        }
        if ($pack && $pack != 'all') {
            $adids = $this->getPackAdspaceIds($pack);
            if (!$adids) $adids = [-1];
            $where[] = "adspaceid in (" . implode(',', $adids) . ')';
        }

        //use logdate_id index
        $beg = (int) date('Ymd');
        $end = $beg;
        //range date
        $rangeDate = $this->I('range_date');
        if ($rangeDate) {       
            $dates = explode(' - ', $rangeDate);
            $startDate = $dates[0];
            $endDate = $startDate;
            if (isset($dates[1])) {
                $endDate = $dates[1];
            }
            $beg = str_replace('/', '-', $startDate);
            $beg = str_replace('-', '', $beg);
            $end = str_replace('/', '-', $endDate);
            $end = str_replace('-', '', $end);
        } 
        if (!$isTotal) {
            $where[] = " logdate_id >= '$beg' and logdate_id <= '$end' ";
        }
        if (!$this->S('is_root')) {
            $ads = $this->S('domainAdvertisers');
            $ids = [];
            if ($ads) {
                foreach($ads as $ad) {
                    $ids[] = $ad['id'];
                }   
                $userId = implode(' , ', $ids);
                $where[] = " advertiser_id in ( $userId ) ";
            } else {
                $where[] = " advertiser_name= ' ' ";
            }
        }
        if (count($where) > 0) {
            $where = ' where '. implode(' and ', $where);
        } else {
            $where = '';
        }
        return $where;
    }

    public function getReportTotal() {
        $parts = $this->_searchParts();
        $where = $this->_searchWhere();
        $sql = "select sum(rd.origin_cost) origin_cost,
                       $parts
                from {{%rpt_sumadbyday}} as rd 
                $where ";
        $data = $this->queryOne($sql);
        if (is_null($data['origin_cost'])) {
            if(in_array($this->S('user_rank'), ['agent', 'advertiser'])) {
                return [0.00, 0, 0, 0.00, 0.00,0.00 ];
            }
            return [[0.00, 0.00],
                    0, 0, 0.00, 
                    [0.00, 0.00],
                    [0.00, 0.00] ];
        }
        if(in_array($this->S('user_rank'), ['agent', 'advertiser'])) {
            $total = [
                 $data['cost'],
                 //$data['reqs'],
                 //$data['bids'],
                 $data['shows'],
                 $data['clicks'],
                 $data['clickRate'],
                 $data['cpm'],
                 $data['cpc'],
            ];
        } else {
            $oc = $data['origin_cost'];
            $cpm = $cpc = '0.00';
            if ($data['shows']) {
                $cpm = number_format($oc/$data['shows']*1000, 2);
            }
            if ($data['clicks']) {
                $cpc = number_format($oc/$data['clicks'], 2);
            }
            $total = [
                [$data['cost'], $oc],
                 //$data['reqs'],
                 //$data['bids'],
                 $data['shows'],
                 $data['clicks'],
                 $data['clickRate'],
                [$data['cpm'], $cpm],
                [$data['cpc'], $cpc],
            ];
        }
        return $total;
    }

    public function getPackAdspaceIds($packId)
    {
        $apps = $this->getAll('ResourceApp', ['status' => 1, 'resource_pack_id' => $packId]);
        $appIds = array_column($apps, 'id');
        $ads = $this->getAll('ResourceAppAdspaceMap', ['status' => 1, 'app_id' => $appIds, 'adspace_type' => 3]);
        $adIds = array_column($ads, 'adspace_id');
        return $adIds;
    }

    public function getReportDataList($matrix) {
        $parts = $this->_searchParts();
        $where = $this->_searchWhere();
        $sql = "select ifnull(rd.campaign_name, 'unknown') campaign_name,
                       campaign_id,
                       ifnull(rd.creative_name, 'unknown') creative_name,
                       creativeuuid,
                       ifnull(rd.seller_name, 'unknown') seller_name,
                       ifnull(rd.advertiser_name, 'unknown') advertiser_name,
                       ifnull(rd.solution_name, 'unknown') solution_name, 
                       solutionid,
                       rd.adspaceid, rd.adspace_name, rd.solutionid,rd.logdate date,
                       $parts
                       from {{%rpt_sumadbyday}} as rd
                       $where
                       group by rd.logdate,
                                rd.exchangeid, 
                                rd.campaign_id,
                                rd.creativeuuid,
                                rd.adspaceid,
                                rd.solutionid,
                                rd.advertiser_id ";
        if (in_array($this->S('user_rank'), ['agent', 'advertiser'])) {
            $sql = "select ifnull(rd.campaign_name, 'unknown') campaign_name,
                           ifnull(rd.seller_name, 'unknown') seller_name,
                           ifnull(rd.advertiser_name, 'unknown') advertiser_name,
                           ifnull(rd.solution_name, 'unknown') solution_name, 
                           rd.adspaceid, rd.adspace_name, 
                           rd.solutionid, rd.logdate date,
                           $parts
                           from {{%rpt_sumadbyday}} as rd
                           $where
                           group by rd.logdate,
                                    rd.campaign_id,
                                    rd.advertiser_id ";
        }
        return $this->queryAll($sql, $matrix);
    }

    /**
     * get all report data on search and group field
     */
    public function getReportPartsOn($key, $isTotal=false) {
        $parts = $this->_searchParts();
        $where = $this->_searchWhere($isTotal);
        $sql = " select rd.$key, $parts
                 from {{%rpt_sumadbyday}} as rd
                 $where
                 group by rd.$key ";

        $datas = $this->queryAll($sql);
        $group = [];
        foreach ($datas as $data) {
            $group[$data[$key]] = $data;
        }
        return $group;
    }

    public function getCampaignReportData($isTotal=false) {
        return $this->getReportPartsOn('campaign_id', $isTotal);
    }

    public function getSolutionReportData($isTotal=false) {
        return $this->getReportPartsOn('solutionid', $isTotal);
    }

    public function getCreativeReportData() {
        return $this->getReportPartsOn('creativeuuid');
    }

    public function getAdNetworkLableCampaignSolutionLevel() {
        $advertiserIds = array_keys($this->S('domainAdvertisers'));
        $where = '';
        if (!$advertiserIds) return [$this->toJson([]),$this->toJson([]),$this->toJson([])];
        if ($advertiserIds) {
            $ids = implode(',', $advertiserIds);
            $where = ' advertiser_id in ('.$ids.') ';
        }

        $solutions = $this->getAll('Solution', $where);
        $labels = $this->getAll('CampaignLabel', $where);
        $campaigns = $this->getAll('Campaign', $where);
        $campaigns = $this->getField($campaigns, ['id', 'name', 'label_id']);

        $labelOnCampaign = [];
        $campaignOnSolution = [];
        $solutionOnCreative = [];

        $solutionToCreative = [];
        if ($where) $creativeWhere = $where.' and status = 1';
        if (!$where) $creativeWhere = 'status = 1';
        $solutionCreativeMaps = $this->getAll('SolutionCreativeMap', $creativeWhere);

        foreach ($solutionCreativeMaps as $map) {
            $solutionToCreative[$map['solution_name']]['id'] = $map['solution_id'];
            $solutionToCreative[$map['solution_name']]['value'][$map['id']] = ['id' => $map['creative_id'], 'value' => $map['creative_name']];
        }

        foreach ($solutions as $solution) {
            $campaignId = $solution['campaign_id'];
            $solutionName = $solution['name'];
            $campaign = $campaigns[$campaignId];
            $campaignName = $campaign['name'];
            $labelId = $campaign['label_id'];

            if (!$labelId || is_null($labelId)) continue;
            $label = $labels[$labelId];
            $labelName = $label['name'];

            if (!isset($solutionToCreative[$solutionName])) continue;
            $solutionOnCreative[$labelName][$solutionName] = $solutionToCreative[$solutionName];

            $campaignOnSolution[$labelId.'. '.$labelName][$campaignName]['id'] = $campaignId;
            $campaignOnSolution[$labelId.'. '.$labelName][$campaignName]['value'][$solution['id']] = ['id' => $solution['id'], 'value' => $solution['name']];

            $labelOnCampaign[$labelId.'. '.$labelName]['id'] = $labelId;
            $labelOnCampaign[$labelId.'. '.$labelName]['value'][$campaignId] = ['id' => $campaignId, 'value' => $campaignName];

        }

        foreach ($labelOnCampaign as &$labels) {
            $labels['value'] = array_values($labels['value']);
        }
        foreach ($campaignOnSolution as &$solutions) {
            foreach ($solutions as $campaign => &$solution) {
                $solution['value'] = array_values($solution['value']);
            }
        }
        foreach ($solutionOnCreative as &$crts) {
            foreach ($crts as $lable => &$crt) {
                $crt['value'] = array_values($crt['value']);
            }
        }
        return [
            json_encode($labelOnCampaign, JSON_UNESCAPED_UNICODE), 
            json_encode($campaignOnSolution, JSON_UNESCAPED_UNICODE),
            json_encode($solutionOnCreative, JSON_UNESCAPED_UNICODE)
        ];
    }

    public function getSolutionCosts($solutionId, $toYesterday=false) {
        $cost = 0; 
        $end = 0;
        if($toYesterday) {
            list($beg, $end) = $this->getDateIdByFlag(1);
        }
        $sql = "select round(sum(cost), 2) cost from {{%rpt_sumadbyday}} ";
        $where = "solutionid = $solutionId";
        $end && $where = "logdate_id <= $end and ".$where;
        $sql .= ' where '.$where;
        $cost = $this->query($sql);
        $cost = array_shift($cost);
        $cost = $cost['cost'];
        return $cost;
    }

    public function getSolutionToCreative() {
        $where = 'status = 1';
        if (!$this->S('is_root')) {
            $us = new US;
            $user = $this->M('User', $this->S('user_id'));
            $advertisers = $us->getUserAdvertisers($user);
            $adKeys = [];
            if ($advertisers) $adKeys = array_keys($advertisers);
            if ($adKeys) {
                $adKeys = implode(',', $adKeys);
                $where .= " and advertiser_id in ($adKeys) ";
            }
        }
        $solutionToCreative = [];
        $solutionCreativeMaps = $this->getAll('SolutionCreativeMap', $where);
        foreach ($solutionCreativeMaps as $map) {
            $solutionToCreative[$map['solution_name']]['id'] = $map['solution_id'];
            $solutionToCreative[$map['solution_name']]['value'][] = ['id' => $map['creative_id'], 'value' => $map['creative_name']];
        }
        return $this->toJson($solutionToCreative);
    }

    public function exportReportData($filter='') {
        $matrix = $this->exportMatrix;
        $this->S('language') == 'en-US' && $matrix = $this->enExportMatrix;
        if (in_array($this->S('user_rank'), ['agent', 'advertiser'])) {
            $matrix = $this->exportFilterMatrix;
            $this->S('language') == 'en-US' && $matrix = $this->enExportFilterMatrix;
        }
        if ($filter == 'true') {
            if (isset($matrix['请求数'])) unset($matrix['请求数']);
            if (isset($matrix['竞价数'])) unset($matrix['竞价数']);
            if (isset($matrix['展示数'])) unset($matrix['展示数']);
            if (isset($matrix['点击率(%)'])) unset($matrix['点击率(%)']);
            if (isset($matrix['cpm'])) unset($matrix['cpm']);
            if (isset($matrix['Requests'])) unset($matrix['Requests']);
            if (isset($matrix['Bids'])) unset($matrix['Bids']);
            if (isset($matrix['Shows'])) unset($matrix['Shows']);
            if (isset($matrix['Click Rate(%)'])) unset($matrix['Click Rate(%)']);
        }
        $exportData = $this->getReportDataList($matrix);
        $xls = $this->t('report', 'Campaign Report');
        $sheet = $xls;
        $excel = new ExcelService;
        $excel->exportXls($exportData, $xls, $sheet);
    }

    public function getReportSolutionDateRange($solutionId) {
        $sql = "select max(logdate_id) beg, min(logdate_id) end
                from {{%rpt_sumadbyday}}
                where solutionid = $solutionId";
        $dates = $this->query($sql);
        if ($dates) {
            $beg = $dates[0]['beg'];
            $end = $dates[0]['end'];
            if (!$beg) return false;
            return [$beg, $end];
        }
        return false;
    }
}//end
